package com.softer.wxzj.service.impl;

import com.alibaba.excel.read.metadata.ReadSheet;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.softer.wxzj.common.entity.CommonResult;
import com.softer.wxzj.common.entity.Page;
import com.softer.wxzj.common.enums.ShiroResultEnum;
import com.softer.wxzj.common.error.CourseException;
import com.softer.wxzj.common.excel.ExcelUtil;
import com.softer.wxzj.common.logic.CalculateAmount;
import com.softer.wxzj.common.util.ShiroUserUtil;
import com.softer.wxzj.common.util.StringUtils;
import com.softer.wxzj.common.util.UUIDUtil;
import com.softer.wxzj.entity.*;
import com.softer.wxzj.mapper.*;
import com.softer.wxzj.service.ExcelService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.math.BigDecimal;
import java.time.LocalDate;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author Gong Zhiyang
 * @date 2020/7/3
 */
@Service
public class ExcelServiceImpl implements ExcelService {
    private final String YES = "是";
    private final String NO = "否";
    private final String HAVE = "有";
    private final String NOTHING = "无";
    private final String AREA = "区";
    @Autowired
    private ExcelMapper excelMapper;
    @Autowired
    private BaseRegionInfoMapper baseRegionInfoMapper;
    @Autowired
    private BaseOrganizationMapper baseOrganizationMapper;
    @Autowired
    private BaseRegionInfoServiceImpl baseRegionInfoService;
    @Autowired
    private BaseSubRegionInfoMapper baseSubRegionInfoMapper;
    @Autowired
    private BaseBuildInfoMapper baseBuildInfoMapper;
    @Autowired
    private BaseBuildInfoServiceImpl baseBuildInfoService;
    @Autowired
    private BaseBankInfoMapper baseBankInfoMapper;
    @Autowired
    private DicContentMapper diccontentMapper;
    @Autowired
    private BaseCellInfoMapper baseCellInfoMapper;
    @Autowired
    private BaseCellInfoServiceImpl baseCellInfoService;
    @Autowired
    private BaseHouseInfoMapper baseHouseInfoMapper;
    @Autowired
    private DepModeInfoMapper depModeInfoMapper;
    @Autowired
    private CalculateAmount calculateAmount;
    @Autowired
    private BaseOwnerInfoMapper baseOwnerInfoMapper;
    @Autowired
    private BaseOwnerInfoServiceImpl baseOwnerInfoService;
    @Autowired
    private DepReqMapper depReqMapper;
    @Autowired
    private DepReqListInfoMapper depReqListInfoMapper;

    /**
     * 上传文件
     *
     * @param file 文件
     * @return 上传结果
     * @throws IOException 异常
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public CommonResult upload(MultipartFile file) throws IOException {
        List<ReadSheet> sheetList = ExcelUtil.getSheetList(file, ExcelBuilding.class);
        if (sheetList == null || sheetList.size() < 3) {
            return CommonResult.ResponseResult(0, "Excel表的sheet数量最少为3个，请重新维护Excel数据");
        }
        //小区ID
        String regionId = UUIDUtil.getUUID();
        //第一个sheet是小区
        Map regionMap = ExcelUtil.readExcel(file, ExcelRegion.class, 0);
        //第二个sheet是分区
        Map subRegionMap = ExcelUtil.readExcel(file, ExcelSubRegion.class, 1);
        //剩下的是自然幢sheet
        List<Map> buildList = ExcelUtil.readMultipleExcel(file, sheetList.size(), ExcelBuilding.class, 2);
        //校验sheet
        CommonResult result = checkList(regionMap, subRegionMap, buildList);
        if (ShiroResultEnum.FAIL.getCode() == result.getData()) {
            return result;
        }
        //先判断临时表中是否存在相同的小区，如果存在，先删除
        CommonResult dealResult = dealList(regionMap, subRegionMap, buildList, regionId);
        if (ShiroResultEnum.FAIL.getCode() == dealResult.getData()) {
            return dealResult;
        }
        return CommonResult.idResult(1, regionId);
    }

    /**
     * 撤销导入
     *
     * @param regionId 小区ID
     * @return 撤销结果
     */
    @Override
    public CommonResult revoke(String regionId) {
        //删除缓存表中的有关小区的所有数据
        int num = excelMapper.deleteById(regionId);
        if (num <= 0) {
            return CommonResult.ResponseResult(0, "删除失败");
        }
        return CommonResult.ResponseResult(1);
    }

    /**
     * 确认导入
     *
     * @param regionId 小区ID
     * @param zoneCode 行政区域编码
     * @return 导入结果
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public CommonResult confirm(String regionId, String zoneCode) {
        //查询小区信息
        ExcelRegion excelRegion = excelMapper.getRegion(regionId);
        //查询分区信息
        List<ExcelSubRegion> subRegionList = excelMapper.getSubRegion(regionId);
        //查询自然幢信息
        List<ExcelBuilding> buildingList = excelMapper.getBuilding(regionId);
        //处理小区信息
        String id = dealRegion(excelRegion, zoneCode);
        //处理分区信息
        dealSubRegion(subRegionList, id);
        // 处理自然幢信息
        dealBuilding(buildingList, id, zoneCode, subRegionList.size());
        //删除数据
        excelMapper.deleteById(regionId);
        return CommonResult.ResponseResult(1);
    }

    /**
     * 分页查询批量导入的信息
     *
     * @param regionId 小区ID
     * @param page     分页实体
     * @return 房屋列表
     */
    @Override
    public PageInfo<ExcelBuilding> getBuildingList(String regionId, Page page) {
        PageHelper.startPage(page.getPageNum(), page.getPageSize());
        List<ExcelBuilding> list = excelMapper.getBuildingList(regionId);
        return new PageInfo<>(list);
    }

    /**
     * 查询小区信息
     *
     * @param regionId 小区ID
     * @return 小区信息
     */
    @Override
    public ExcelRegion getInfo(String regionId) {
        return excelMapper.getRegion(regionId);
    }

    /**
     * 处理自然幢信息
     *
     * @param buildingList 自然幢集合
     * @param id           小区ID
     */
    private void dealBuilding(List<ExcelBuilding> buildingList, String id, String zoneCode, int subRegionSize) {
        //自然幢名称列表
        List<String> nameList = buildingList.stream().map(p -> p.getBuildName()).collect(Collectors.toList());
        if (nameList != null && !nameList.isEmpty()) {
            //去重
            List<String> collect = nameList.stream().distinct().collect(Collectors.toList());
            for (String buildName : collect) {
                //查询某一号楼的所有数据
                List<ExcelBuilding> buildList = buildingList.stream().filter(p -> buildName.equals(p.getBuildName())).collect(Collectors.toList());
                //处理某一号楼的数据
                dealBuildList(buildList, id, zoneCode, subRegionSize);
            }
        }
    }

    /**
     * 处理某号楼的数据
     *
     * @param buildList 某个自然幢的数据
     * @param id        小区ID
     */
    private void dealBuildList(List<ExcelBuilding> buildList, String id, String zoneCode, int subRegionSize) {
        //判断当前自然幢是否已经存在
        ExcelBuilding excelBuilding = buildList.get(0);
        BaseBuildInfo baseBuildInfo = baseBuildInfoMapper.getInfoByName(excelBuilding.getBuildName(), excelBuilding.getSubRegionNo(), id);
        if (baseBuildInfo != null) {
            //修改自然幢
            baseBuildInfo.setModifier(ShiroUserUtil.getUserName());
            setBuildParam(baseBuildInfo, excelBuilding, id, zoneCode, subRegionSize);
            int num = baseBuildInfoMapper.update(baseBuildInfo);
            if (num == 0) {
                throw new CourseException(ShiroResultEnum.FAIL.getCode(), baseBuildInfo.getBuildName() + "修改自然幢失败");
            }
        } else {
            //新增自然幢
            baseBuildInfo = new BaseBuildInfo();
            baseBuildInfo.setId(UUIDUtil.getUUID());
            addBuildInfo(baseBuildInfo, excelBuilding, id, zoneCode, subRegionSize);
        }
        String buildId = baseBuildInfo.getId();
        //处理自然幢的单元数据
        dealCellList(buildList, buildId);
    }

    /**
     * 处理自然幢的单元数据
     *
     * @param buildList 自然幢的数据集合
     * @param buildId   自然幢ID
     */
    private void dealCellList(List<ExcelBuilding> buildList, String buildId) {
        //单元名称列表
        List<String> cellNameList = buildList.stream().map(p -> p.getCellName()).collect(Collectors.toList());
        if (cellNameList != null && !cellNameList.isEmpty()) {
            //去重
            List<String> collect = cellNameList.stream().distinct().collect(Collectors.toList());
            int cellNo = 0;
            for (String cellName : collect) {
                //查询某单元的所有数据
                List<ExcelBuilding> cellList = buildList.stream().filter(p -> cellName.equals(p.getCellName())).collect(Collectors.toList());
                //处理某单元的数据
                cellNo = baseCellInfoService.getCellNo(buildId, cellNo);
                dealHouseUseCellList(cellList, cellName, buildId, cellNo);
            }
        }
    }

    public void dealHouseUseCellList(List<ExcelBuilding> cellList, String cellName, String buildId, int cellNo) {
        //查询相同单元的不同楼层属性
        List<String> houseUseList = cellList.stream().map(p -> p.getHouseUse()).collect(Collectors.toList());
        if (houseUseList != null && !houseUseList.isEmpty()) {
            //去重
            List<String> collect = houseUseList.stream().distinct().collect(Collectors.toList());
            for (String houseUse : collect) {
                if (houseUse == null) {
                    throw new CourseException(ShiroResultEnum.FAIL.getCode(), cellList.get(0).getBuildName() + "存在未维护房屋性质的房间，请重新维护房屋性质");
                }
                //查询某单元的所有数据
                List<ExcelBuilding> houseUseCellList = cellList.stream().filter(p -> houseUse.equals(p.getHouseUse())).collect(Collectors.toList());
                //处理某单元的数据
                dealCell(houseUseCellList, cellName, buildId, cellNo, houseUse);
            }
        }
    }

    /**
     * 处理单元数据
     *
     * @param cellList 某单元集合
     * @param cellName 单元名称
     * @param buildId  自然幢名称
     */
    private void dealCell(List<ExcelBuilding> cellList, String cellName, String buildId, int cellNo, String houseUse) {
        String cellType = diccontentMapper.getIdByNameAndType(houseUse, "housingUse");
        //查询该单元名称是否存在，不存在，则新增；存在，不处理
        String cellId = baseCellInfoMapper.getInfoByName(cellName, buildId, cellType);
        if (StringUtils.isEmpty(cellId)) {
            BaseCellInfo baseCellInfo = new BaseCellInfo();
            cellId = UUIDUtil.getUUID();
            baseCellInfo.setId(cellId);
            baseCellInfo.setBuildNo(buildId);
            baseCellInfo.setCellNo(String.format("%04d", cellNo));
            baseCellInfo.setCellName(cellName);
            baseCellInfo.setState("1");
            String elevator = getElevator(cellList.get(0).getLift(), "");
            baseCellInfo.setElevator(elevator);
            baseCellInfo.setCellType(cellType);
            baseCellInfo.setHouseNum(getHouseNum(cellList));
            //查询自然幢sheet名称集合
            List<String> floorNoList = cellList.stream().map(p -> p.getFloorNo()).collect(Collectors.toList());
            Object[] objects = floorNoList.toArray();
            Arrays.sort(objects);
            baseCellInfo.setStartFloor(String.valueOf(objects[0]));
            baseCellInfo.setEndFloor(String.valueOf(objects[floorNoList.size() - 1]));
            int num = baseCellInfoMapper.insert(baseCellInfo);
            if (num == 0) {
                throw new CourseException(ShiroResultEnum.FAIL.getCode(), cellName + "新增单元失败");
            }
        }
        dealHouseList(cellList, cellId, buildId, cellName);
    }

    /**
     * 处理房间数据
     *
     * @param cellList 单元数据集合
     * @param cellId   单元ID
     * @param buildId  自然幢ID
     */
    private void dealHouseList(List<ExcelBuilding> cellList, String cellId, String buildId, String cellName) {
        if (cellList != null && !cellList.isEmpty()) {
            for (ExcelBuilding excelBuilding : cellList) {
                String roomNo = String.format("%03d", Integer.parseInt(excelBuilding.getRoomNo()));
                //根据自然幢ID，单元ID，楼层，房间号查询正式库中是否存在该房间
                BaseHouseInfo baseHouseInfo = baseHouseInfoMapper.getInfoByName(buildId, cellId, excelBuilding.getFloorNo(), roomNo);
                int num;
                BigDecimal firstDue;
                if (baseHouseInfo != null) {
                    //查询是否存在过缴存申请
                    String reqNo = depReqMapper.getNoByHouseId(baseHouseInfo.getId());
                    if (StringUtils.isNotEmpty(reqNo)) {
                        //存在过缴存申请，不对该房间进行操作
                        continue;
                    }
                    //TODO 查看缴存中是否存在房间记录，（先将缴存数据改为撤销状态，后续根据需要做逻辑删除）
                    depReqListInfoMapper.updateStateByHouseId(baseHouseInfo.getId(), "0");
                    //存在，更新房间信息
                    baseHouseInfo.setModifier(ShiroUserUtil.getUserName());
                    firstDue = setHouseParam(baseHouseInfo, excelBuilding, roomNo, cellId, buildId, cellName);
                    num = baseHouseInfoMapper.update(baseHouseInfo);
                } else {
                    //不存在，新增
                    baseHouseInfo = new BaseHouseInfo();
                    baseHouseInfo.setId(UUIDUtil.getUUID());
                    baseHouseInfo.setCreater(ShiroUserUtil.getUserName());
                    firstDue = setHouseParam(baseHouseInfo, excelBuilding, roomNo, cellId, buildId, cellName);
                    num = baseHouseInfoMapper.insertExcel(baseHouseInfo);
                }
                String msg = cellName + excelBuilding.getFloorNo() + "层" + excelBuilding.getRoomNo();
                if (num == 0) {
                    throw new CourseException(ShiroResultEnum.FAIL.getCode(), msg + "房间更新失败");
                }
                dealOwner(excelBuilding, baseHouseInfo.getId(), msg, buildId, firstDue);
            }
        }
    }

    /**
     * 处理业主信息
     *
     * @param excelBuilding 业主数据
     * @param id            房间ID
     * @param buildId       自然幢ID
     * @param firstDue      初交应交
     */
    private void dealOwner(ExcelBuilding excelBuilding, String id, String msg, String buildId, BigDecimal firstDue) {
        if (StringUtils.isNotEmpty(excelBuilding.getOwnerName())) {
            //如果当前房间存在业主，则将原业主变为旧业主，然后新增业主；如果不存在业主，直接添加业主
            BaseOwnerInfo infoByAccNo = baseOwnerInfoMapper.getInfoByAccNo(id);
            String userName = ShiroUserUtil.getUserName();
            if (infoByAccNo != null) {
                int num = baseOwnerInfoMapper.updateOwnerState(id, "0", userName);
                if (num == 0) {
                    throw new CourseException(ShiroResultEnum.FAIL.getCode(), "修改业主失败");
                }
            }
            addOwner(excelBuilding, id, msg, buildId, firstDue);
        }
    }

    /**
     * 新增业主信息
     *
     * @param excelBuilding 业主数据
     * @param id            房间ID
     * @param buildId       自然幢ID
     * @param firstDue      初交应交
     */
    private void addOwner(ExcelBuilding excelBuilding, String id, String msg, String buildId, BigDecimal firstDue) {
        BaseOwnerInfo baseOwnerInfo = new BaseOwnerInfo();
        String ownerId = UUIDUtil.getUUID();
        baseOwnerInfo.setId(ownerId);
        baseOwnerInfo.setAccNo(id);
        baseOwnerInfo.setOwnerNo(baseOwnerInfoService.ownerNo(id));
        String certType = diccontentMapper.getIdByNameAndType(excelBuilding.getCertType(), "classificationOfValidDocuments");
        if (StringUtils.isEmpty(certType)) {
            throw new CourseException(ShiroResultEnum.FAIL.getCode(), msg + "证件类型维护错误");
        }
        baseOwnerInfo.setCertType(certType);
        baseOwnerInfo.setCertNo(excelBuilding.getCertNo());
        baseOwnerInfo.setName(excelBuilding.getOwnerName());
        baseOwnerInfo.setPhone(excelBuilding.getPhone());
        baseOwnerInfo.setState("1");
        baseOwnerInfo.setInfoType("0");
        baseOwnerInfo.setCreater(ShiroUserUtil.getUserName());
        baseOwnerInfo.setPrice(new BigDecimal(excelBuilding.getPrice() != null ? excelBuilding.getPrice() : "0"));
        baseOwnerInfo.setPurchaseDate(excelBuilding.getPurchaseDate());
        baseOwnerInfo.setIsAffiliated("0");
        int num = baseOwnerInfoMapper.insert(baseOwnerInfo);
        if (num == 0) {
            throw new CourseException(ShiroResultEnum.FAIL.getCode(), "新增业主失败");
        }
        //新增缴存维护
        addDepReqList(excelBuilding, id, msg + excelBuilding.getOwnerName(), buildId, firstDue, ownerId);
    }

    /**
     * 新增缴存维护
     *
     * @param excelBuilding 业主数据
     * @param houseId       房间ID
     * @param buildId       自然幢ID
     * @param firstDue      初交应交
     * @param ownerId       业主ID
     */
    private void addDepReqList(ExcelBuilding excelBuilding, String houseId, String msg, String buildId, BigDecimal firstDue, String ownerId) {
        if (StringUtils.isNotEmpty(excelBuilding.getFirstDue()) && StringUtils.isNotEmpty(excelBuilding.getReqDate())) {
            //如果存在缴存金额，则新增一条缴存维护
            DepReqListInfo depReqListInfo = new DepReqListInfo();
            depReqListInfo.setId(UUIDUtil.getUUID());
            depReqListInfo.setDepType("1");
            depReqListInfo.setBuildId(buildId);
            depReqListInfo.setHouseId(houseId);
            depReqListInfo.setDueAmount(firstDue);
            depReqListInfo.setPayAmount(new BigDecimal(excelBuilding.getFirstDue()));
            depReqListInfo.setFlowState("2");
            depReqListInfo.setState("2");
            depReqListInfo.setCreater(ShiroUserUtil.getUserName());
            depReqListInfo.setDepDate(excelBuilding.getReqDate());
            depReqListInfo.setOwnerNo(ownerId);
            int num = depReqListInfoMapper.insert(depReqListInfo);
            if (num == 0) {
                throw new CourseException(ShiroResultEnum.FAIL.getCode(), msg + "新增缴存维护失败");
            }
        }
    }

    /**
     * 设置房间的公共参数
     *
     * @param baseHouseInfo 房间信息
     * @param excelBuilding 临时表数据
     * @param roomNo        房号
     */
    private BigDecimal setHouseParam(BaseHouseInfo baseHouseInfo, ExcelBuilding excelBuilding, String roomNo, String cellId, String buildId, String cellName) {
        baseHouseInfo.setRoomNo(roomNo);
        String roomName = excelBuilding.getFloorNo() + String.format("%02d", Integer.parseInt(excelBuilding.getRoomNo()));
        baseHouseInfo.setRoomName(roomName);
        baseHouseInfo.setBuildNo(buildId);
        baseHouseInfo.setCellNo(cellId);
        String name = excelMapper.getRegionName(buildId);
        StringBuilder str = new StringBuilder(6);
        str.append(name).append(cellName).append(roomName);
        baseHouseInfo.setHouseSite(str.toString());
        baseHouseInfo.setFloorNo(excelBuilding.getFloorNo());
        String housingUse = diccontentMapper.getIdByNameAndType(excelBuilding.getHouseUse(), "housingUse");
        if (StringUtils.isEmpty(housingUse)) {
            throw new CourseException(ShiroResultEnum.FAIL.getCode(), str.toString() + "房屋性质维护错误");
        }
        baseHouseInfo.setHouseUse(housingUse);
        String houseType = diccontentMapper.getIdByNameAndType(excelBuilding.getHouseAttr(), "houseType");
        if (StringUtils.isEmpty(houseType)) {
            throw new CourseException(ShiroResultEnum.FAIL.getCode(), str.toString() + "房屋类型维护错误");
        }
        baseHouseInfo.setHouseAttr(houseType);
        baseHouseInfo.setStruArea(new BigDecimal(excelBuilding.getStruArea()));
        String bankId = baseBankInfoMapper.getIdByBankName(excelBuilding.getBankCode());
        if (StringUtils.isEmpty(bankId)) {
            throw new CourseException(ShiroResultEnum.FAIL.getCode(), str.toString() + "承办银行维护错误");
        }
        baseHouseInfo.setBankCode(bankId);
        baseHouseInfo.setState(StringUtils.isEmpty(excelBuilding.getOwnerName()) ? "0" : "1");
        String firstMode = depModeInfoMapper.getIdByName(excelBuilding.getFirstMode());
        if (StringUtils.isEmpty(firstMode)) {
            throw new CourseException(ShiroResultEnum.FAIL.getCode(), str.toString() + "交存模式维护错误");
        }
        baseHouseInfo.setFirstMode(firstMode);
        baseHouseInfo.setPrice(new BigDecimal(excelBuilding.getPrice() != null ? excelBuilding.getPrice() : "0"));
        BigDecimal firstDue = calculateAmount.calculateDueAmount(baseHouseInfo);
        baseHouseInfo.setFirstDue(firstDue);
        baseHouseInfo.setPurchaseDate(excelBuilding.getPurchaseDate());
        baseHouseInfo.setFlowInProgress(StringUtils.isEmpty(excelBuilding.getFirstDue()) ? "0" : "deposit");
        baseHouseInfo.setOwnerState(StringUtils.isEmpty(excelBuilding.getOwnerName()) ? "0" : "1");
        baseHouseInfo.setIsAffiliated("0");
        return firstDue;
    }


    /**
     * 查询每层户数
     *
     * @param cellList 单元集合
     * @return 每层最大的户数
     */
    private String getHouseNum(List<ExcelBuilding> cellList) {
        Map<String, Long> collect = cellList.stream().collect(Collectors.groupingBy(ExcelBuilding::getFloorNo, Collectors.counting()));
        Collection<Long> values = collect.values();
        Object[] objects = values.toArray();
        Arrays.sort(objects);
        return String.valueOf(objects[collect.size() - 1]);
    }

    /**
     * 新增自然幢
     *
     * @param baseBuildInfo 自然幢
     * @param excelBuilding 临时表数据
     * @param id            小区ID
     */
    private void addBuildInfo(BaseBuildInfo baseBuildInfo, ExcelBuilding excelBuilding, String id, String zoneCode, int subRegionSize) {
        String buildNo = baseBuildInfoService.getBuildNo();
        baseBuildInfo.setBuildNo(buildNo);
        baseBuildInfo.setRegionNo(id);
        setBuildParam(baseBuildInfo, excelBuilding, id, zoneCode, subRegionSize);
        int num = baseBuildInfoMapper.insert(baseBuildInfo);
        if (num == 0) {
            throw new CourseException(ShiroResultEnum.FAIL.getCode(), baseBuildInfo.getBuildName() + "新增自然幢失败");
        }
    }

    /**
     * 设置自然幢的公共参数
     *
     * @param baseBuildInfo 自然幢数据
     * @param excelBuilding 临时表数据
     * @param id            小区Id
     */
    public void setBuildParam(BaseBuildInfo baseBuildInfo, ExcelBuilding excelBuilding, String id, String zoneCode, int subRegionSize) {
        BaseRegionInfo baseRegionInfo = baseRegionInfoMapper.getInfo(id);
        String msg = excelBuilding.getBuildName() + excelBuilding.getCellName() + excelBuilding.getFloorNo() + "层" + excelBuilding.getRoomNo() + "号房间";
        String subRegionNo = excelBuilding.getSubRegionNo();
        if (StringUtils.isNotEmpty(subRegionNo)) {
            BaseSubRegionInfo baseSubRegionInfo = excelMapper.getSubRegionByNo(subRegionNo, id);
            if (baseSubRegionInfo == null) {
                throw new CourseException(ShiroResultEnum.FAIL.getCode(), msg + "中的分区编号与分区表中的分区编号不匹配，请重新维护");
            }
            baseBuildInfo.setSubRegionNo(baseSubRegionInfo.getId());
        } else {
            if (subRegionSize > 0) {
                throw new CourseException(ShiroResultEnum.FAIL.getCode(), msg + "中的分区编号与分区表中的分区编号不匹配，请重新维护");
            }
        }
        //查询银行ID
        String bankId = baseBankInfoMapper.getIdByBankName(excelBuilding.getBankCode());
        if (StringUtils.isEmpty(bankId)) {
            throw new CourseException(ShiroResultEnum.FAIL.getCode(), msg + "银行信息不匹配，请重新维护");
        }
        baseBuildInfo.setBankCode(bankId);
        baseBuildInfo.setZoneCode(zoneCode);
        baseBuildInfo.setAddress(baseRegionInfo.getAddress());
        baseBuildInfo.setBuildName(excelBuilding.getBuildName());
        baseBuildInfo.setState("1");
        //查询楼层属性的ID
        String buildTypeId = diccontentMapper.getIdByNameAndType(excelBuilding.getBuildType(), "naturalUilding");
        if (StringUtils.isEmpty(buildTypeId)) {
            throw new CourseException(ShiroResultEnum.FAIL.getCode(), msg + "楼层属性不匹配，请重新维护");
        }
        baseBuildInfo.setBuildType(buildTypeId);
        //查询结构属性
        String buildAttrId = diccontentMapper.getIdByNameAndType(excelBuilding.getBuildAttr(), "naturalBuildingStructure");
        if (StringUtils.isEmpty(buildAttrId)) {
            throw new CourseException(ShiroResultEnum.FAIL.getCode(), msg + "结构属性不匹配，请重新维护");
        }
        baseBuildInfo.setBuildAttr(buildAttrId);
        String sortType = excelBuilding.getSortType();
        if ("按单元组合".equals(sortType)) {
            baseBuildInfo.setSortType("0");
        } else if ("按层组合".equals(sortType)) {
            baseBuildInfo.setSortType("1");
        } else {
            throw new CourseException(ShiroResultEnum.FAIL.getCode(), msg + "房屋规则不匹配，请重新维护");
        }
        baseBuildInfo.setRecDate(LocalDate.now().toString());
        baseBuildInfo.setFlowState("2");
        String elevator = getElevator(excelBuilding.getLift(), msg);
        baseBuildInfo.setElevator(elevator);
        baseBuildInfo.setCreater(ShiroUserUtil.getUserName());
        baseBuildInfo.setDevelopNo(baseRegionInfo.getDevelopNo());
        baseBuildInfo.setEnvironNo(baseRegionInfo.getPropertyNo());
    }

    /**
     * 获取是否有电梯数据
     *
     * @param elevator 临时表数据
     * @param msg      具体楼信息
     * @return 是否有电梯
     */
    private String getElevator(String elevator, String msg) {
        if (HAVE.equals(elevator)) {
            return "1";
        } else if (NOTHING.equals(elevator)) {
            return "0";
        } else {
            throw new CourseException(ShiroResultEnum.FAIL.getCode(), msg + "有无电梯不匹配，只能填写‘有’或者‘无’");
        }
    }

    /**
     * 处理分区信息
     *
     * @param subRegionList 分区信息
     * @param id            小区ID
     */
    private void dealSubRegion(List<ExcelSubRegion> subRegionList, String id) {
        if (!subRegionList.isEmpty()) {
            for (ExcelSubRegion excelSubRegion : subRegionList) {
                String subRegionName = excelSubRegion.getSubRegionName();
                subRegionName = subRegionName.endsWith(AREA) ? subRegionName : subRegionName + AREA;
                //查询当前小区是否存在该分区，存在则修改，不存在，则新增
                BaseSubRegionInfo baseSubRegionInfo = baseSubRegionInfoMapper.getInfoByName(id, subRegionName);
                excelSubRegion.setSubRegionName(subRegionName);
                int num;
                String userName = ShiroUserUtil.getUserName();
                if (baseSubRegionInfo != null) {
                    //修改分区
                    baseSubRegionInfo.setModifier(userName);
                    setSubRegionParam(baseSubRegionInfo, excelSubRegion, id);
                    num = baseSubRegionInfoMapper.update(baseSubRegionInfo);
                } else {
                    baseSubRegionInfo = new BaseSubRegionInfo();
                    //新增分区
                    baseSubRegionInfo.setId(excelSubRegion.getId());
                    setSubRegionParam(baseSubRegionInfo, excelSubRegion, id);
                    num = baseSubRegionInfoMapper.insert(baseSubRegionInfo);
                }
                if (num == 0) {
                    throw new CourseException(ShiroResultEnum.FAIL.getCode(), "分区信息维护失败");
                }
            }
        }
    }

    /**
     * 设置分区公共参数
     *
     * @param baseSubRegionInfo 分区信息
     * @param excelSubRegion    临时表信息
     */
    private void setSubRegionParam(BaseSubRegionInfo baseSubRegionInfo, ExcelSubRegion excelSubRegion, String id) {
        baseSubRegionInfo.setRegionNo(id);
        baseSubRegionInfo.setSubRegionNo(excelSubRegion.getSubRegionNo());
        baseSubRegionInfo.setSubRegionName(excelSubRegion.getSubRegionName());
        baseSubRegionInfo.setRemark(excelSubRegion.getRemark());
        baseSubRegionInfo.setState("1");
    }

    /**
     * 处理小区信息
     *
     * @param excelRegion 小区信息
     */
    private String dealRegion(ExcelRegion excelRegion, String zoneCode) {
        String developId = baseOrganizationMapper.getInfoByName(excelRegion.getDevelopName());
        if (StringUtils.isEmpty(developId)) {
            throw new CourseException(ShiroResultEnum.FAIL.getCode(), "小区表中的开发企业{" + excelRegion.getDevelopName() + "}在系统中不存在，请先维护");
        }
        String propertyId = null;
        if (StringUtils.isNotEmpty(excelRegion.getPropertyName())) {
            propertyId = baseOrganizationMapper.getInfoByName(excelRegion.getPropertyName());
            if (StringUtils.isEmpty(propertyId)) {
                throw new CourseException(ShiroResultEnum.FAIL.getCode(), "小区表中的物业企业{" + excelRegion.getPropertyName() + "}在系统中不存在，请先维护");
            }
        }
        // 暂时定义：如果小区表中存在相同的小区名称，则对小区进行修改，不存在，则添加
        BaseRegionInfo baseRegionInfo = baseRegionInfoMapper.getInfoByRegionName(excelRegion.getRegionName());
        int num;
        String userName = ShiroUserUtil.getUserName();
        if (baseRegionInfo != null) {
            baseRegionInfo.setModifier(userName);
            setRegionParam(baseRegionInfo, excelRegion, developId, propertyId, zoneCode);
            //修改小区
            num = baseRegionInfoMapper.updateById(baseRegionInfo);
        } else {
            baseRegionInfo = new BaseRegionInfo();
            baseRegionInfo.setId(excelRegion.getId());
            String regNo = baseRegionInfoService.getRegNo();
            baseRegionInfo.setRegionNo(regNo);
            baseRegionInfo.setState("2");
            baseRegionInfo.setRecDate(LocalDate.now().toString());
            baseRegionInfo.setRecOper(userName);
            baseRegionInfo.setCreater(userName);
            baseRegionInfo.setFlowState("2");
            setRegionParam(baseRegionInfo, excelRegion, developId, propertyId, zoneCode);
            //新增小区
            num = baseRegionInfoMapper.insert(baseRegionInfo);
        }
        if (num == 0) {
            throw new CourseException(ShiroResultEnum.FAIL.getCode(), "小区信息维护失败");
        }
        return baseRegionInfo.getId();
    }

    /**
     * 设置小区的公共参数
     *
     * @param baseRegionInfo 小区信息
     * @param excelRegion    临时表数据
     * @param developId      开发企业ID
     * @param propertyId     物业企业ID
     */
    private void setRegionParam(BaseRegionInfo baseRegionInfo, ExcelRegion excelRegion, String developId, String propertyId, String zoneCode) {
        baseRegionInfo.setZoneCode(zoneCode);
        baseRegionInfo.setDevelopNo(developId);
        baseRegionInfo.setPropertyNo(propertyId);
        baseRegionInfo.setAddress(excelRegion.getAddress());
        baseRegionInfo.setSubRegionFlag(YES.equals(excelRegion.getSubRegionFlag()) ? "1" : "0");
        baseRegionInfo.setStruArea(excelRegion.getStruArea());
        baseRegionInfo.setRegionName(excelRegion.getRegionName());
    }

    /**
     * 处理sheet信息
     *
     * @param regionMap    小区sheet
     * @param subRegionMap 分区sheet
     * @param buildList    自然幢sheet
     * @param regionId     小区ID
     * @return 处理结果
     */
    public CommonResult dealList(Map regionMap, Map subRegionMap, List<Map> buildList, String regionId) {
        //根据小区名称，查询临时表中是否存在相同小区
        List<ExcelRegion> excelRegions = (List<ExcelRegion>) regionMap.get("list");
        ExcelRegion excelRegion = excelRegions.get(0);
        ExcelRegion region = excelMapper.getRegionByRegionName(excelRegion.getRegionName());
        if (region != null) {
            //删除缓存表中的有关小区的所有数据
            int num = excelMapper.deleteById(region.getId());
            if (num <= 0) {
                return CommonResult.ResponseResult(0, "存在历史数据未处理，请联系管理员");
            }
        }
        excelRegion.setId(regionId);
        //新增小区
        addRegion(excelRegion);
        //新增分区信息
        addSubRegion(subRegionMap, regionId);
        //新增自然幢信息
        addBuild(buildList, regionId);
        return CommonResult.ResponseResult(1);
    }

    /**
     * 新增自然幢信息
     *
     * @param buildList 自然幢信息
     * @param regionId  小区ID
     */
    public void addBuild(List<Map> buildList, String regionId) {
        List<ExcelBuilding> allList = new ArrayList<>();
        for (Map map : buildList) {
            String sheetName = (String) map.get("sheetName");
            List<ExcelBuilding> list = (List<ExcelBuilding>) map.get("list");
            for (ExcelBuilding excelBuilding : list) {
                excelBuilding.setBuildName(sheetName);
                excelBuilding.setRegionId(regionId);
                if (!excelBuilding.getCellName().endsWith("单元")) {
                    excelBuilding.setCellName(excelBuilding.getCellName() + "单元");
                }
            }
            allList.addAll(list);
        }
        int num = excelMapper.addBuild(allList);
        if (num == 0) {
            throw new CourseException(ShiroResultEnum.FAIL.getCode(), "导入自然幢信息失败");
        }
    }

    /**
     * 新增分区信息
     *
     * @param subRegionMap 分区信息
     * @param regionId     小区ID
     */
    public void addSubRegion(Map subRegionMap, String regionId) {
        List<ExcelSubRegion> list = (List<ExcelSubRegion>) subRegionMap.get("list");
        if (!list.isEmpty()) {
            for (ExcelSubRegion excelSubRegion : list) {
                excelSubRegion.setRegionId(regionId);
            }
            int num = excelMapper.addSubRegion(list);
            if (num == 0) {
                throw new CourseException(ShiroResultEnum.FAIL.getCode(), "导入分区信息失败");
            }
        }
    }

    /**
     * 新增小区
     *
     * @param excelRegion 小区信息
     * @return 新增结果
     */
    public void addRegion(ExcelRegion excelRegion) {
        int num = excelMapper.addRegion(excelRegion);
        if (num != 1) {
            throw new CourseException(ShiroResultEnum.FAIL.getCode(), "导入小区信息失败");
        }
    }

    /**
     * 校验各个sheet
     *
     * @param regionMap    小区sheet
     * @param subRegionMap 分区sheet
     * @param buildList    自然幢sheet
     * @return 校验结果
     */
    private CommonResult checkList(Map regionMap, Map subRegionMap, List<Map> buildList) {
        List<ExcelRegion> excelRegions = (List<ExcelRegion>) regionMap.get("list");
        if (excelRegions == null) {
            return CommonResult.ResponseResult(0, "请维护小区信息");
        }
        if (excelRegions.size() == 0) {
            return CommonResult.ResponseResult(0, "请维护小区信息");
        }
        if (excelRegions.size() > 1) {
            return CommonResult.ResponseResult(0, "请只维护一个小区的信息");
        }
        ExcelRegion excelRegion = excelRegions.get(0);
        CommonResult regionResult = checkExcelRegion(excelRegion);
        if (ShiroResultEnum.FAIL.getCode() == regionResult.getData()) {
            return regionResult;
        }
        //查询分区信息（如果填的有分区，但是分区sheet中没有数据，则返回，如果填的没有分区，但分区sheet中有数据，也返回）
        String subRegionFlag = excelRegion.getSubRegionFlag();
        CommonResult suRegionResult = checkSubRegionList(subRegionFlag, subRegionMap);
        if (ShiroResultEnum.FAIL.getCode() == suRegionResult.getData()) {
            return suRegionResult;
        }
        //校验自然幢数据
        CommonResult buildResult = checkBuildList(buildList);
        if (ShiroResultEnum.FAIL.getCode() == buildResult.getData()) {
            return buildResult;
        }
        return CommonResult.ResponseResult(1);
    }

    /**
     * 校验自然幢数据
     *
     * @param buildList 自然幢列表
     * @return 校验结果
     */
    private CommonResult checkBuildList(List<Map> buildList) {
        if (buildList == null) {
            return CommonResult.ResponseResult(0, "请维护自然幢信息");
        }
        //查询自然幢sheet名称集合
        List<String> sheetNameList = buildList.stream().map(p -> (String) p.get("sheetName")).collect(Collectors.toList());
        if (!checkRepeatData(sheetNameList)) {
            return CommonResult.ResponseResult(0, "自然幢名称重复，请重新编辑");
        }
        return CommonResult.ResponseResult(1);
    }

    /**
     * 检验分区信息是否正确
     *
     * @param subRegionFlag 是否存在分区
     * @param subRegionMap  分区数据
     * @return 校验结果
     */
    private CommonResult checkSubRegionList(String subRegionFlag, Map subRegionMap) {
        List<ExcelSubRegion> list = (List<ExcelSubRegion>) subRegionMap.get("list");
        if (list == null) {
            return CommonResult.ResponseResult(0, "请添加分区信息sheet");
        }
        if (YES.equals(subRegionFlag)) {
            //如果填的有分区，但是分区sheet中没有数据，则返回
            if (list.isEmpty()) {
                return CommonResult.ResponseResult(0, "小区信息中的是否分区字段与分区信息表中的数据不匹配，请重新维护");
            }
        } else if (NO.equals(subRegionFlag)) {
            if (!list.isEmpty()) {
                return CommonResult.ResponseResult(0, "小区信息中的是否分区字段与分区信息表中的数据不匹配，请重新维护");
            }
        } else {
            return CommonResult.ResponseResult(0, "小区表中的是否存在分区字段信息错误，只能填写‘是’或者‘否’");
        }
        //校验分区编号是否重复
        List<String> subRegionNoList = list.stream().map(p -> p.getSubRegionNo()).collect(Collectors.toList());
        if (!checkRepeatData(subRegionNoList)) {
            return CommonResult.ResponseResult(0, "分区编号存在重复数据，请重新维护");
        }
        //校验分区名称是否重复
        List<String> subRegionNameList = list.stream().map(p -> p.getSubRegionName()).collect(Collectors.toList());
        if (!checkRepeatData(subRegionNameList)) {
            return CommonResult.ResponseResult(0, "分区名称存在重复数据，请重新维护");
        }
        return CommonResult.ResponseResult(1);
    }

    /**
     * 校验list中是否存在重复数据
     *
     * @param list 集合
     * @return 校验结果
     */
    private boolean checkRepeatData(List<String> list) {
        long count = list.stream().distinct().count();
        if (count != list.size()) {
            return false;
        }
        return true;
    }

    /**
     * 校验小区参数
     *
     * @param excelRegion 小区信息
     * @return 校验结果
     */
    private CommonResult checkExcelRegion(ExcelRegion excelRegion) {
        if (StringUtils.isEmpty(excelRegion.getRegionName())) {
            return CommonResult.ResponseResult(0, "小区名称为空");
        }
        if (StringUtils.isEmpty(excelRegion.getSubRegionFlag())) {
            return CommonResult.ResponseResult(0, "是否分区为空");
        }
        if (StringUtils.isEmpty(excelRegion.getDevelopName())) {
            return CommonResult.ResponseResult(0, "开发企业为空");
        }
        return CommonResult.ResponseResult(1);
    }
}
